libname hcup1999 '/homes/data/hcup/sid/1999';
libname sidwork '/disk/data3/sid.work/millers/1999';

***By state;
proc sql;
create table sidwork.totalallbystate as select 
sum(ecode) as ecode,
sum(Chronic) as HCUPChronic,
sum(injury) as injury,
sum(count) as count,
sum(preg) as preg,
bmonth as bmonth,
byear as byear,
fips as fips
from sidwork.sid1999
group by bmonth, byear, fips;
quit;

proc sql;
create table sidwork.totalblackbystate as select 
sum(ecode) as ecode,
sum(Chronic) as HCUPChronic,
sum(injury) as injury,
sum(count) as count,
sum(preg) as preg,
bmonth as bmonth,
byear as byear,
fips as fips
from sidwork.sid1999
where race=2
group by bmonth, byear, fips;
quit;


proc sql;
create table sidwork.totalnotblackbystate as select 
sum(ecode) as ecode,
sum(Chronic) as HCUPChronic,
sum(injury) as injury,
sum(count) as count,
sum(preg) as preg,
bmonth as bmonth,
byear as byear,
fips as fips
where race NE 2 and FIPS NE 41
from sidwork.sid1999
group by bmonth, byear, fips;
quit;

***Combined, not aggregated by state;

proc sql;
create table sidwork.totalall as select 
sum(ecode) as ecode,
sum(Chronic) as HCUPChronic,
sum(injury) as injury,
sum(count) as count,
sum(preg) as preg,
bmonth as bmonth,
byear as byear
from sidwork.sid1999
group by bmonth, byear;
quit;


data sidwork.totalall;
set sidwork.totalall;
if preg=. then preg=0;
if count=. then count=0;
run;



proc sql;
create table sidwork.totalblack as select 
sum(ecode) as ecode,
sum(Chronic) as HCUPChronic,
sum(injury) as injury,
sum(count) as count,
sum(preg) as preg,
bmonth as bmonth,
byear as byear
from sidwork.sid1999
where race=2 
group by bmonth, byear;
quit;

data sidwork.totalblack;
set sidwork.totalblack;
if preg=. then preg=0;
if count=. then count=0;
run;

proc sql;
create table sidwork.totalnotblack as select 
sum(ecode) as ecode,
sum(Chronic) as HCUPChronic,
sum(injury) as injury,
sum(count) as count,
sum(preg) as preg,
bmonth as bmonth,
byear as byear
from sidwork.sid1999
where race NE 2 and FIPS NE 41
group by bmonth, byear;
quit;

data sidwork.totalnotblack;
set sidwork.totalnotblack;
if preg=. then preg=0;
if count=. then count=0;
run;

